﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace SGArchiveSystem7xDB.Sql
{
   public class SGTongji
    {
        /// <summary>
        /// 汇总统计年度和类型
        /// </summary>
        /// <returns></returns>
        public static List<TongjiNDandLX> Tongji_NDandLX()
        {
            string sql = "SELECT AR_NAME, (CASE BGQXNAME WHEN '永久' THEN COUNT(*) ELSE 0 END) as '永久', (CASE BGQXNAME WHEN '10年' THEN COUNT(*) ELSE 0 END) as '10年' ," +
                "(CASE BGQXNAME WHEN '30年' THEN COUNT(*) ELSE 0 END) as '30年' ,ND FROM V_ARCHIVEBGQXMJ GROUP BY AR_NAME, BGQXNAME, ND ORDER BY ND, AR_NAME";

           // string sql = "SELECT COUNT(*) AS COUNT, ND,AR_NAME,BGQXNAME FROM V_ARCHIVEBGQXMJ GROUP BY ND,AR_NAME,BGQXNAME";
            SGArchiveSystem7xEntities database = new SGArchiveSystem7xEntities();
            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            try
            {
                conn.ConnectionString = database.Database.Connection.ConnectionString;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                ///总数
                cmd.CommandText = sql;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                List<TongjiNDandLX> list = new List<TongjiNDandLX>();
                foreach(DataRow dr in dt.Rows)
                {
                    TongjiNDandLX en = new TongjiNDandLX();
                    en.name = dr["AR_NAME"].ToString();
                    en.year = dr["ND"] == null ? "" : dr["ND"].ToString();
                    en.y1 = int.Parse(dr["永久"].ToString());
                    en.y2 = int.Parse(dr["30年"].ToString());
                    en.y3 = int.Parse(dr["10年"].ToString());
                    en.count = en.y1 + en.y2 + en.y3;
                    list.Add(en);
                }
                return list;
            }
            catch { }
            finally
            {
                conn.Close();
            }

            return null;
        }

        /// <summary>
        /// 汇总统计年度和部门
        /// </summary>
        /// <returns></returns>
        public static List<TongjiNDandLX> Tongji_NDandBM()
        {
            string sql = "SELECT dep_name, (CASE BGQXNAME WHEN '永久' THEN COUNT(*) ELSE 0 END) as '永久', (CASE BGQXNAME WHEN '10年' THEN COUNT(*) ELSE 0 END) as '10年' ," +
                "(CASE BGQXNAME WHEN '30年' THEN COUNT(*) ELSE 0 END) as '30年' ,ND FROM V_ARCHIVEBGQXMJ GROUP BY dep_name, BGQXNAME, ND ORDER BY ND, dep_name";

           // string sql = "SELECT COUNT(*) AS COUNT, ND,AR_NAME,BGQXNAME FROM V_ARCHIVEBGQXMJ GROUP BY ND,AR_NAME,BGQXNAME";
           SGArchiveSystem7xEntities database = new SGArchiveSystem7xEntities();
            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            try
            {
                conn.ConnectionString = database.Database.Connection.ConnectionString;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                ///总数
                cmd.CommandText = sql;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                List<TongjiNDandLX> list = new List<TongjiNDandLX>();
                foreach (DataRow dr in dt.Rows)
                {
                    TongjiNDandLX en = new TongjiNDandLX();
                    en.name = dr["dep_name"].ToString();
                    en.year = dr["ND"] == null ? "" : dr["ND"].ToString();
                    en.y1 = int.Parse(dr["永久"].ToString());
                    en.y2 = int.Parse(dr["30年"].ToString());
                    en.y3 = int.Parse(dr["10年"].ToString());
                    en.count = en.y1 + en.y2 + en.y3;
                    list.Add(en);
                }
                return list;
            }
            catch { }
            finally
            {
                conn.Close();
            }

            return null;
        }

        /// <summary>
        /// 汇总统计部门和类型
        /// </summary>
        /// <returns></returns>
        public static List<TongjiNDandLX> Tongji_BMandLX()
        {
            SGArchiveSystem7xEntities database = new SGArchiveSystem7xEntities();

            string sql = "SELECT AR_NAME, (CASE BGQXNAME WHEN '永久' THEN COUNT(*) ELSE 0 END) as '永久', (CASE BGQXNAME WHEN '10年' THEN COUNT(*) ELSE 0 END) as '10年' ," +
                "(CASE BGQXNAME WHEN '30年' THEN COUNT(*) ELSE 0 END) as '30年' ,dep_name FROM V_ARCHIVEBGQXMJ GROUP BY AR_NAME, BGQXNAME, dep_name ORDER BY dep_name, AR_NAME";

            // string sql = "SELECT COUNT(*) AS COUNT, ND,AR_NAME,BGQXNAME FROM V_ARCHIVEBGQXMJ GROUP BY ND,AR_NAME,BGQXNAME";

            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            try
            {
                conn.ConnectionString = database.Database.Connection.ConnectionString;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                ///总数
                cmd.CommandText = sql;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                List<TongjiNDandLX> list = new List<TongjiNDandLX>();
                foreach (DataRow dr in dt.Rows)
                {
                    TongjiNDandLX en = new TongjiNDandLX();
                    en.name = dr["AR_NAME"].ToString();
                    en.year = dr["dep_name"] == null ? "" : dr["dep_name"].ToString();
                    en.y1 = int.Parse(dr["永久"].ToString());
                    en.y2 = int.Parse(dr["30年"].ToString());
                    en.y3 = int.Parse(dr["10年"].ToString());
                    en.count = en.y1 + en.y2 + en.y3;
                    list.Add(en);
                }
                return list;
            }
            catch { }
            finally
            {
                conn.Close();
            }

            return null;
        }


        /// <summary>
        /// 自定义字段统计
        /// </summary>
        /// <param name="table"></param>
        /// <param name="fieldsA"></param>
        /// <param name="fieldsB"></param>
        /// <param name="aid"></param>
        /// <returns></returns>
        public static DataTable Tongji_Archive(string table, string fieldsA, string fieldsB, int aid)
        {
            string sql = "select  count(*) as n," + fieldsA + " as F1," + fieldsB + " as F2 from " + table + " where ARID=" + aid + " group by " + fieldsA + ", " + fieldsB;
            SGArchiveSystem7xEntities database = new SGArchiveSystem7xEntities();
            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            try
            {
                conn.ConnectionString = database.Database.Connection.ConnectionString;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }


                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                ///总数
                cmd.CommandText = sql;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                conn.Close();//连接需要关闭  
                conn.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                conn.Close();
                conn.Dispose();
                return null;
            }
        }

        /// <summary>
        /// 区域时间内的档案类型
        /// </summary>
        /// <returns></returns>
        public static List<TongjiEn> Tongji_BrrAtype(string sdate, string edate)
        {
            ///利用档案分类
            string sql1 = "SELECT COUNT(*) as count,BI_ATYPE as n FROM V_BINFO where";

            if(sdate != null && sdate != "")
                sql1 += " BU_DATE >='" + sdate + "' and";
            if (edate != null && edate != "")
                sql1 += " BU_DATE <='" + edate + "'";

            if (sql1.EndsWith("where"))
                sql1 = sql1.Substring(0, sql1.LastIndexOf("w"));
            else if (sql1.EndsWith("and"))
                sql1 = sql1.Substring(0, sql1.LastIndexOf("a"));

            sql1 += " GROUP BY BI_ATYPE";

            SGArchiveSystem7xEntities database = new SGArchiveSystem7xEntities();
            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            try
            {
                conn.ConnectionString = database.Database.Connection.ConnectionString;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                ///总数
                cmd.CommandText = sql1;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return BindTongji(dt);
            }
            catch { }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            return null;
        }
        /// <summary>
        /// 区域时间内的利用目的
        /// </summary>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <returns></returns>
        public static List<TongjiEn> Tongji_BrrLymd(string sdate, string edate)
        {
            ///利用目录分类
            string sql1 = "SELECT COUNT(*) as count,BU_LYMD as n FROM V_BINFO where";

            if (sdate != null && sdate != "")
                sql1 += " BU_DATE >='" + sdate + "' and";
            if (edate != null && edate != "")
                sql1 += " BU_DATE <='" + edate + "'";

            if (sql1.EndsWith("where"))
                sql1 = sql1.Substring(0, sql1.LastIndexOf("w"));
            else if (sql1.EndsWith("and"))
                sql1 = sql1.Substring(0, sql1.LastIndexOf("a"));

            sql1 += " GROUP BY BU_LYMD";

            SGArchiveSystem7xEntities database = new SGArchiveSystem7xEntities();
            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            try
            {
                conn.ConnectionString = database.Database.Connection.ConnectionString;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                ///总数
                cmd.CommandText = sql1;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return BindTongji(dt);
            }
            catch { }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            return null;
        }
        /// <summary>
        /// 区域时间内的借阅类型
        /// </summary>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <returns></returns>
        public static List<TongjiEn> Tongji_BrrType(string sdate, string edate)
        {
            ///利用方式分类  1. 电子， 2. 实体
            string sql1 = "SELECT COUNT(*) as count,BU_TYPE as n FROM V_BINFO where";

            if (sdate != null && sdate != "")
                sql1 += " BU_DATE >='" + sdate + "' and";
            if (edate != null && edate != "")
                sql1 += " BU_DATE <='" + edate + "'";

            if (sql1.EndsWith("where"))
                sql1 = sql1.Substring(0, sql1.LastIndexOf("w"));
            else if (sql1.EndsWith("and"))
                sql1 = sql1.Substring(0, sql1.LastIndexOf("a"));

            sql1 += " GROUP BY bu_type";

            SGArchiveSystem7xEntities database = new SGArchiveSystem7xEntities();
            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            try
            {
                conn.ConnectionString = database.Database.Connection.ConnectionString;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                ///总数
                cmd.CommandText = sql1;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                var list = BindTongji(dt);
                foreach (var n in list)
                {
                    if (n.name == "1")
                        n.name = "电子借阅";
                    else
                        n.name = "纸质借阅";
                }
                return list;
            }
            catch { }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            return null;
        }
        /// <summary>
        /// 区域时间内的借阅类型
        /// </summary>
        /// <param name="sdate"></param>
        /// <param name="edate"></param>
        /// <returns></returns>
        public static List<TongjiEn> Tongji_BmType(string sdate, string edate)
        {
            ///利用方式分类  1. 电子， 2. 实体
            string sql1 = "SELECT COUNT(*) as count,BU_TYPE as n FROM V_BINFO GROUP BY bu_type where";

            if (sdate != null && sdate != "")
                sql1 += " BU_DATE >='" + sdate + "' and";
            if (edate != null && edate != "")
                sql1 += " BU_DATE <='" + edate + "'";

            if (sql1.EndsWith("where"))
                sql1 = sql1.Substring(0, sql1.LastIndexOf("w"));
            else if (sql1.EndsWith("and"))
                sql1 = sql1.Substring(0, sql1.LastIndexOf("a"));

            SGArchiveSystem7xEntities database = new SGArchiveSystem7xEntities();
            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            try
            {
                conn.ConnectionString = database.Database.Connection.ConnectionString;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                ///总数
                cmd.CommandText = sql1;

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return BindTongji(dt);
            }
            catch { }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

            return null;
        }

        private static List<TongjiEn> BindTongji(DataTable  dt) 
        {
            if (dt == null || dt.Rows.Count == 0)
                return null;
            else
            {
                List<TongjiEn> list = new List<TongjiEn>();
                foreach(DataRow dr in dt.Rows)
                {
                    TongjiEn en = new TongjiEn();
                    en.name = dr["n"].ToString();
                    en.value = int.Parse(dr["count"].ToString());
                    list.Add(en);
                }
                return list;
            }
        }
    }

    public class TongjiEn
    {
        public int value { get; set; }
        public string name { get; set; }
    }

    public class TongjiNDandLX
    {
        public int count { get; set; }
        public string year { get; set; }

        public int y1 { get; set; }

        public int y2 { get; set; }

        public int y3 { get; set; }

        public string name { get; set; }
    }
}
